package dao;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Annual_report;
import bean.Content;
import database.Database;

public class Annual_reportDao {
	public ArrayList<Annual_report> getAllAnnual_report() {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,annual_report_title,annual_report_date,scan_num from annual_report order by annual_report_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			ArrayList<Annual_report> annual_report_list = new ArrayList<Annual_report>();
			while (rs.next()) {
				Annual_report annual_report = new Annual_report();
				annual_report.setId(rs.getInt(1));
				annual_report.setAnnual_report_title(rs.getString(2));
				annual_report.setAnnual_report_date(rs.getString(3).substring(
						0, 10));
				annual_report.setScan_num(rs.getInt(4));
				annual_report_list.add(annual_report);
			}
			return annual_report_list;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ArrayList queryConditionAnnual_report(String message,
			String date_start, String date_end)
			throws UnsupportedEncodingException {
		Connection conn = null;
		ArrayList<Annual_report> annual_report_list = new ArrayList<Annual_report>();
		try {
			conn = Database.getConnection();
			String temp_sql = "";
			if (date_start != null && !"".equals(date_start)
					&& date_end != null && !"".equals(date_end)) {
				temp_sql = "and annual_report_date between '" + date_start
						+ "' and '" + date_end + "'";
			}
			String sql = "select id,annual_report_title,annual_report_date,scan_num from annual_report where annual_report_title like '%"
					+ message
					+ "%' "
					+ temp_sql
					+ " order by annual_report_date desc";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				Annual_report annual_report = new Annual_report();
				annual_report.setId(rs.getInt(1));
				annual_report.setAnnual_report_title(rs.getString(2));
				annual_report.setAnnual_report_date(rs.getString(3).substring(
						0, 10));
				annual_report.setScan_num(rs.getInt(4));
				annual_report_list.add(annual_report);
			}
			prep.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return annual_report_list;
	}

	/**
	 * 
	 * @param title
	 * @param date
	 * @return
	 */
	public int deleteAnnual_reportItem(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "delete from annual_report where id = " + id;
			PreparedStatement prep = conn.prepareStatement(sql);
			int num = prep.executeUpdate();
			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public Content getContent(String id, int index) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,annual_report_title,data,annual_report_date,scan_num from annual_report where id="
					+ id;
			PreparedStatement prep = null;
			if (index == 2) {
				String sql2 = "update annual_report set scan_num = scan_num + 1 where id = "
						+ id;
				prep = conn.prepareStatement(sql2);
				prep.executeUpdate();
			}
			prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();
			rs.next();
			Content content = new Content();
			content.setId(rs.getInt(1));
			content.setTitle(rs.getString(2));

			String data = rs.getString(3);
			if (data != null) {
				data = data.replaceAll("\r\n", "");
				data = data.replaceAll("\r", "");
				data = data.replaceAll("\n", "");
				data = data.replaceAll("\"", "'");
			}
			content.setData(data);
			content.setDate(rs.getString(4));
			content.setScan_num(rs.getInt(5));

			prep.close();
			return content;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	public int updateContent(String id, String modifyTitle, String data) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "update annual_report set annual_report_title=? , data=? where id="
					+ id;
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, modifyTitle);
			prep.setString(2, data);
			int num = prep.executeUpdate();

			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public int insertItem(String insertTitle, String data, String date) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "insert into annual_report(annual_report_title,annual_report_date,data,scan_num) values(?,?,?,?);";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, insertTitle);
			prep.setString(2, date);
			prep.setString(3, data);
			prep.setInt(4, 0);
			int num = prep.executeUpdate();

			prep.close();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
}
